__file__ = 'A18.py'
__author__ = 'Jerry Liu'
__date__ = '2016-04-15'

"""
    问题：将 第 0015 题中的 city.xls 文件中的内容写到 city.xml 文件中，如下所示：
        <?xmlversion="1.0" encoding="UTF-8"?>
        <root>
        <citys>
        <!--
            城市信息
        -->
        {
            "1" : "上海",
            "2" : "北京",
            "3" : "成都"
        }
        </citys>
        </root>
"""

from openpyxl.reader.excel import load_workbook
from xml.dom.minidom import Document

def getdata(path):
    wb = load_workbook(path)
    # 读取第一个sheet
    snames = wb.get_sheet_names()
    sheet = wb.get_sheet_by_name(snames[0])

    cxml = Document()
    eRoot =cxml.createElement('root')
    cxml.appendChild(eRoot)
    eCities = cxml.createElement('cities')
    eRoot.appendChild(eCities)
    eComments = cxml.createComment('城市信息')
    eCities.appendChild(eComments)

    # 定义字典
    dict = {}
    for line in range(sheet.get_highest_row()):
        line += 1
        id = sheet.cell(row=line, column=1).value
        city = sheet.cell(row=line, column=2).value
        dict[id] = city

    eCities.appendChild(cxml.createTextNode(str(dict)))

    f = open('d://city.xml', 'w')
    cxml.writexml(f)
    f.close()

if __name__ == '__main__':
    getdata('d://city.xlsx')
